import csv
import re

import requests
import json
import time
import pymysql

from pyquery import PyQuery as pq

dbhost = '47.106.139.236'

dbuser = 'root'

passwd = r'123456'

dbname = 'douban'

conn = pymysql.connect(dbhost, dbuser, passwd, dbname, charset='utf8')

cur = conn.cursor()
key = '绿色青浦'
header = ['title','content','url','postime','is_top','is_orig','readnum','likenum','wxname']
sql = 'select title,content,url,postime,is_top,is_orig,readnum,likenum,name from wx where name = "{}"'.format(key)
cur.execute(sql)
data_list = cur.fetchall()
# for title,content,url,postime,is_top,is_orig,readnum,likenum,name in data_list:
with open('{}_weixin_reslut.csv'.format(key), 'w', encoding='gb18030', newline='') as s:
    writer = csv.writer(s)
    writer.writerow(header)
    writer.writerows(data_list)

#
#     # time.sleep(0.5)
#     # doc = pq(url)
#     #
#     # original = doc('div .rich_media_meta_list #copyright_logo').text()
#     # contents = doc('div.rich_media_content').text()
#     #
#     # content = re.compile(u'[\U00010000-\U0010ffff]').sub(u'', contents)
#     #
#     # top = re.findall(re.compile(r'idx=(\d+)&'), str(url))[0]
#     # if top == '1':
#     #     top = '头条'
#     # sql = "update wx set content =%s,is_top=%s,is_orig=%s where id =%s"
#     # cur.execute(sql,(content,top,original,id))
#     # conn.commit()
#     # print(id,url)
# keywords = '绿色青浦'
# for i in range(0,50):
#     page = i*10
#     print(i,page)
#     time.sleep(3)
#     url ='https://mp.weixin.qq.com/mp/profile_ext?action=getmsg&__biz=MzA4NjY3MDQxNQ==&f=json&offset={}&count=20&is_ok=1&scene=124&uin=MjE4Mzc2MDA2MA%3D%3D&key=347d6efc8ade2571960a854044815e789b4a07255e5844c38a89e845566c47ecaba94090b3c825ea688247f8c6c4ebf8f4d2ba94812ca02d884b05cad210a8b6cfbb21fe14975fe11c5ab7259d6ea69a&pass_ticket=tdMWMS7gEv36JmkKcJP1%2BL2VpueptugWI0cXRmxb8fmbYullnhtpnF9hwR9Y8xnN&wxtoken=&appmsg_token=977_mDRPfXVU75Nx6ZTgfyK8QhhbWfvpqI8a4TUpNg~~&x5=0&f=json HTTP/1.1'.format(page)
#     header = {'Host':'mp.weixin.qq.com',
# 'Connection':'keep-alive',
# 'Accept':'*/*',
# 'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36 MicroMessenger/6.5.2.501 NetType/WIFI WindowsWechat QBCore/3.43.901.400 QQBrowser/9.0.2524.400',
# 'X-Requested-With':'XMLHttpRequest',
# 'Referer':'https://mp.weixin.qq.com/mp/profile_ext?action=home&__biz=MjM5MTE2ODE4Mg==&scene=124&uin=MjE4Mzc2MDA2MA%3D%3D&key=cc10fc79f81dd1f6038d1268106910af9529a6646e68efccf147adb50b2bd278d61f4a34f933d440ab8c1f9efe25a3e669ba637d7a4fbc1c30fe9727d593e19d2dbf0bd50c3658e08fb5cf7364806f4f&devicetype=Windows+7&version=62060426&lang=zh_CN&a8scene=7&pass_ticket=f%2BhybwdMWHJkOev2ukHJyDGepwvLvxI0prPHmmGv%2FKqGr3aG8NMSlpuXoQjp1J1N&winzoom=1',
# 'Accept-Encoding':'gzip, deflate',
# 'Accept-Language':'zh-CN,zh;q=0.8,en-us;q=0.6,en;q=0.5;q=0.4',
# 'Cookie':'wxuin=2183760060; devicetype=Windows7; version=62060426; lang=zh_CN; pass_ticket=f+hybwdMWHJkOev2ukHJyDGepwvLvxI0prPHmmGv/KqGr3aG8NMSlpuXoQjp1J1N; wap_sid2=CLyRppEIElw1andfZGt3THFtQzkwdzFYQXVsXzhPTHA4TzRGTXd3clZhQ0IzUWQzR0ZXb2NUZmxSZDJHVHBHTEh1cTJyckRaQWFRaEliVm5vcTZwOGVlTHl1SWx5OUFEQUFBfjDInM3dBTgNQJVO',
#
#
#
#     }
#     resp = requests.get(url,headers = header,verify =False).text
#
#
#     resp_json = json.loads(resp)
#     data_list = json.loads(resp_json.get('general_msg_list')).get("list")
#     for item in data_list:
#         try:
#             # print('wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww')
#             # print(item)
#             timeArray = item.get("comm_msg_info").get("datetime")
#             x = time.localtime(timeArray)
#             datetime_ = time.strftime('%Y-%m-%d %H:%M:%S', x)
#
#             sql = "insert into wx(title,content,url,postime,is_top,is_orig,name) values  (%s,%s,%s,%s,%s,%s,%s)"
#
#             num = item.get('app_msg_ext_info').get("is_multi")
#
#             multi_tem_list = item.get('app_msg_ext_info')
#
#             titles = multi_tem_list.get('title')
#             title = re.compile(u'[\U00010000-\U0010ffff]').sub(u'', titles)
#             content_url = multi_tem_list.get("content_url").replace("amp;", '')
#             time.sleep(0.5)
#             doc_resp = requests.get(content_url, verify=False).text
#             doc = pq(doc_resp)
#
#             original = doc('div .rich_media_meta_list #copyright_logo').text()
#             contents = doc('div.rich_media_content').text()
#
#             content = re.compile(u'[\U00010000-\U0010ffff]').sub(u'', contents)
#
#             top = re.findall(re.compile(r'idx=(\d+)&'), str(content_url))[0]
#             if top == '1':
#                 top = '头条'
#
#             print(title, content_url, datetime_)
#
#             cur.execute(sql,(title,content,content_url,datetime_,top,original,keywords))
#             conn.commit()
#             if num == 1:
#                 for info in multi_tem_list.get("multi_app_msg_item_list"):
#                     titles = info.get("title")
#                     title = re.compile(u'[\U00010000-\U0010ffff]').sub(u'', titles)
#                     content_url = info.get("content_url").replace("amp;", '')
#                     doc_resp = requests.get(content_url,verify =False).text
#                     doc = pq(doc_resp)
#
#
#                     original = doc('div .rich_media_meta_list #copyright_logo').text()
#                     contents = doc('div.rich_media_content').text()
#
#                     content = re.compile(u'[\U00010000-\U0010ffff]').sub(u'', contents)
#
#                     top = re.findall(re.compile(r'idx=(\d+)&'), str(content_url))[0]
#                     if top == '1':
#                         top = '头条'
#
#                     print(title, content_url, datetime_)
#
#                     cur.execute(sql, (title, content, content_url, datetime_, top, original, keywords))
#                     conn.commit()
#         except  Exception as e:
#             print(e)

